package com.mx.selenium;
import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Random;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
/**导出excel文件
 *
 * @author 路人甲
 *
 */
public class ExportExcel {

    public static Integer y = 1;

    public ExportExcel()
    {
        initdata();
    }

    /**
     * 初始化数据
     */
    public void initdata()
    {

    }

    /**导出数据以Excel格式导出
     * exportName 导出文件名称
     * @param exportName
     */
    public static void exportPerson(String exportName,List<SFDeptEntity> list)
    {
        try{
            File excelFile = new File("D:/Temp.xls");
            // 文件格式
            WritableCellFormat format = new WritableCellFormat();
            // x  靠左
            format.setAlignment(Alignment.RIGHT);
            // y 靠顶
            format.setVerticalAlignment(VerticalAlignment.TOP);

            // 创建一个工作文件
            WritableWorkbook writableWorkbook = Workbook.createWorkbook(excelFile);
            // 创建一个工作簿
            WritableSheet sheet1 = writableWorkbook.createSheet("个人信息列表", 0);
            //创建行数 设置行的宽度
//            sheet1.setColumnView(0, 10);
//            sheet1.setColumnView(1, 10);
//            sheet1.setColumnView(2, 10);
//            sheet1.setColumnView(3, 10);
            // 设置行的值
            sheet1.addCell(new Label(0, 0, "code", format));
            sheet1.addCell(new Label(1, 0, "address", format));
            sheet1.addCell(new Label(2, 0, "deptCode", format));
            sheet1.addCell(new Label(3, 0, "lat", format));
            sheet1.addCell(new Label(4, 0, "linkman", format));
            sheet1.addCell(new Label(5, 0, "lng", format));
            sheet1.addCell(new Label(6, 0, "name", format));
            sheet1.addCell(new Label(7, 0, "serviceContent", format));
            sheet1.addCell(new Label(8, 0, "serviceTime", format));
            sheet1.addCell(new Label(9, 0, "tel", format));
            sheet1.addCell(new Label(10, 0, "storeType", format));

            // 把集合写入到excel中

            for (SFDeptEntity bean : list)
            {
                int colspanNum = 0;
                sheet1.addCell(new Label(colspanNum++, y, String.valueOf(bean.getCode()), format));
                sheet1.addCell(new Label(colspanNum++, y, String.valueOf(bean.getAddress()), format));
                sheet1.addCell(new Label(colspanNum++, y, String.valueOf(bean.getDeptCode()), format));
                sheet1.addCell(new Label(colspanNum++, y, String.valueOf(bean.getLat()), format));
                sheet1.addCell(new Label(colspanNum++, y, String.valueOf(bean.getLinkman()), format));
                sheet1.addCell(new Label(colspanNum++, y, String.valueOf(bean.getLng()), format));
                sheet1.addCell(new Label(colspanNum++, y, String.valueOf(bean.getName()), format));
                sheet1.addCell(new Label(colspanNum++, y, String.valueOf(bean.getServiceContent()), format));
                sheet1.addCell(new Label(colspanNum++, y, String.valueOf(bean.getServiceTime()), format));
                sheet1.addCell(new Label(colspanNum++, y, String.valueOf(bean.getTel()), format));
                sheet1.addCell(new Label(colspanNum++, y, String.valueOf(bean.getStoreType()), format));
                y++;
            }

            writableWorkbook.write();
            writableWorkbook.close();
//   如果是在网络下载的，那么就写这些
//            super.getHttpServletResponse().setContentType("application/x-msdownload");
//            String encodetittle = new String(excelName.getBytes("GBK"), "ISO-8859-1");
//            super.getHttpServletResponse().addHeader("Content-Disposition","attachment;filename="+encodetittle+".xls");
//            FileInputStream finput = new FileInputStream(excelFile);
//            OutputStream output = super.getHttpServletResponse().getOutputStream();
//            File fout = new File(exportName + ".xls");
//            OutputStream output = new FileOutputStream(fout);
//            BufferedInputStream buffin = new BufferedInputStream(finput);
//            BufferedOutputStream buffout = new BufferedOutputStream(output);
//            byte[] buffer = new byte[4096];
//            int count = 0;
//            while ((count = buffin.read(buffer, 0, buffer.length)) > 0) {
//                buffout.write(buffer, 0, count);
//            }
//            buffin.close();
//            buffout.close();
//            finput.close();
//            output.close();
//            excelFile.delete();
        }
        catch (Exception e) {
            e.printStackTrace();
        }
        finally
        {
            System.out.println("完成导出操作");
        }

    }
    /**导入数据以Excel格式导出
     *
     * @param importExcel 导入Excel文件名称
     */
    public void importPerson(String importExcel)
    {
        try{
            File excelFile = new File(importExcel+".xls");
            // 创建一个工作文件
            Workbook workbook = Workbook.getWorkbook(excelFile);
            // 获得第一个工作簿 这里有两种方法获取sheet表,1为名字，而为下标，从0开始
            Sheet sheet = workbook.getSheet(0);
            // 总记录数
            int allRow = sheet.getRows();
            int allColspan = sheet.getColumns();
            System.out.println(allRow);
            System.out.println(allColspan);
            // 取数据
            for (int i=0; i<allRow; i++)
            {
                // 取出每一列的值
                for (int j=0; j<allColspan; j++)
                {
//     sheet.getCell(列数, 行数);
                    Cell cell = sheet.getCell(j, i);
                    // 打印出该列的值
                    System.out.print(cell.getContents() + "\t");
                }
                System.out.println();
            }
            //关闭
            workbook.close();
        }catch (Exception e) {
            e.printStackTrace();
        }
        finally
        {
            System.out.println("完成导入操作");
        }
    }

    /**
     * @param args
     */
    public static void main(String[] args) {

        ExportExcel exportExcel = new ExportExcel();
        // 设置路径
        String srcPath = "C:/Quarantine/PersonVo";
//  导出
//  exportExcel.exportPerson(srcPath);
        // 读取
        exportExcel.importPerson(srcPath);
    }
}